<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/5/3
 * Time: 12:23
 */

namespace Admin\Model;
use Think\Model;

class DonationModel extends Model
{
    protected $tableName = "donation";
    protected $tablePrefix = "dangjian_";
    protected $showRow = 10;
//    //根据项目查询捐款金额
//    public function queryAllPriceByItem ()
//    {
//        $count = $this->alias("d")
//            ->join("right join dangjian_item i on i.id=d.item_id")
//            ->field("i.id,i.item,sum(d.price) total_price,i.end_time")
//            ->group("i.id")
//            ->select();
//        $count = count($count);
//        $page = new \Think\Page($count,$this->showRow);
//        $show = $page->show();
//        $list = $this->alias("d")
//            ->join("right join dangjian_item i on i.id=d.item_id")
//            ->field("i.id,i.item,sum(d.price) total_price,i.end_time")
//            ->group("d.item_id")
//            ->limit($page->firstRow.','.$page->listRows)
//            ->select();
//        $res['show'] = $show;
//        $res['list'] = $list;
//        return $res;
//    }

//    //根据项目id查询所有捐款记录
//    public function queryAllListByItem ($id)
//    {
//        $count = $this->where("item_id=$id")->count();
////        echo $count;die;
//        $page = new \Think\Page($count,$this->showRow);
//        $show = $page->show();
//        $list = $this->alias("d")
//            ->join("dangjian_item i on i.id=d.item_id")
//            ->join("dangjian_user u on u.id=d.user_id")
//            ->field("d.id,i.item,d.price,i.end_time,u.name")
//            ->where("d.item_id=$id")
//            ->order("d.create_time desc")
//            ->limit($page->firstRow.','.$page->listRows)
//            ->select();
//        $res['show'] = $show;
//        $res['list'] = $list;
//        return $res;
//    }

    //查询总捐款金额
    public function queryAllPrice ($data)
    {
        $name = $data['name'];//捐款人
        $item = $data['item'];//捐款项目
        $time = $data['time'];//捐款时间范围
        $map = array();
        if(!empty($name)){
            $map['u.name'] = array("LIKE", '%' . $name . '%');
        }
        if(!empty($item)){
            $map['d.item_id'] = $item;
        }
        if(!empty($time)){
            $start = substr($time,0,10);
            $end = substr($time,13,10);
            $start = date("Y-m-d H:i:s",strtotime($start));
            $end = date("Y-m-d H:i:s",strtotime($end));
            $map['d.create_time'] = array(array("gt",$start),array("lt",$end),"and");
        }
        $res = $this->alias("d")
            ->join("dangjian_item i on i.id=d.item_id")
            ->join("dangjian_user u on u.id=d.user_id")
            ->field("sum(d.price) totalPrice")->where($map)->find();
        return $res;
    }

//    //按年份查询捐款记录
//    public function queryAllPriceByYear ()
//    {
//        $list = $this->alias("d")
//            ->join("dangjian_item i on i.id=d.item_id")
//            ->field("i.id,i.item,sum(d.price) total_price,date_format(d.create_time,'%Y') year")
//            ->group("date_format(d.create_time,'%Y')")
//            ->select();
//        return $list;
//    }
//
//    public function queryAllListByYear ($year)
//    {
//        $count = $this->where("year(create_time)=$year")->count();
////        echo $count;die;
//        $page = new \Think\Page($count,$this->showRow);
//        $show = $page->show();
//        $list = $this->alias("d")
//            ->join("dangjian_item i on i.id=d.item_id")
//            ->join("dangjian_user u on u.id=d.user_id")
//            ->field("d.id,i.item,d.price,i.end_time,u.name")
//            ->where("year(d.create_time)=$year")
//            ->order("d.create_time desc")
//            ->limit($page->firstRow.','.$page->listRows)
//            ->select();
////        echo "<pre>";print_r($list);die;
//        $res['show'] = $show;
//        $res['list'] = $list;
//        return $res;
//    }

    public function queryAllDon($data)
    {

        $name = $data['name'];//捐款人
        $item = $data['item'];//捐款项目
        $time = $data['time'];//捐款时间范围
        $order = $data['order'];//排序字段
        $order1 = $data['order1'];//排序方式
        $map = array();
        if(!empty($name)){
            $map['u.name'] = array("LIKE", '%' . $name . '%');
        }
        if(!empty($item)){
            $map['d.item_id'] = $item;
        }
        if(!empty($time)){
            $start = substr($time,0,10);
            $end = substr($time,13,10);
            $start = date("Y-m-d H:i:s",strtotime($start));
            $end = date("Y-m-d H:i:s",strtotime($end));
            $map['d.create_time'] = array(array("gt",$start),array("lt",$end),"and");
        }

        if(empty($order)){
            $order = "d.id";
        }

        if(empty($order1)){
            $order1 = "asc";
        }
        $count = $this
            ->alias("d")
            ->join("dangjian_item i on i.id=d.item_id")
            ->join("dangjian_user u on u.id=d.user_id")
            ->where($map)
            ->count();
//        echo $count;die;
        $page = new \Think\Page($count,$this->showRow);
        $show = $page->show();
        $list = $this->alias("d")
            ->join("dangjian_item i on i.id=d.item_id")
            ->join("dangjian_user u on u.id=d.user_id")
            ->field("d.id,i.item,d.price,u.name,d.create_time")
            ->where($map)
            ->order("$order $order1")
            ->limit($page->firstRow.','.$page->listRows)
            ->select();
        $res['show'] = $show;
        $res['list'] = $list;
        return $res;
    }
}